﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using SqlHelp;
using System.Data;
using Model;
using CommonCs;

namespace BugsData
{
    public class BugData
    {
        public DataTable GetAllBugsByType(int size, int index, int id)
        {
            String sql = "select row_number() over (order by a.id) as RN, a.*,b.Uid from Bugs as a,Users as b where a.Createuser = b.Id and a.DeleteTag=0 and a.BugType='" + id + "'";
            SqlParameter[] parm = new SqlParameter[]{
                new SqlParameter("@BugType",id)
            };

            DataTable dt = new DataTable();
            try
            {
                dt = SQLHELP.getdiyerTable(size, index, sql, "Bugs");
                if (dt == null || dt.Rows.Count == 0)
                {
                    return null;

                }
                else
                {
                    return dt;
                }
            }
            catch (Exception)
            {
                Common.WriteFile(CommonCs.Exceptions.Abnormal, "根据type获取buglist[带分页]异常,msg:[" + Exceptions.getbuglistbybugtypeSql + "]!");
                throw new Exception(Exceptions.getbuglistbybugtypeSql);
            }
        }

        public DataTable GetBugsByType(int bugType)
        {
            String sql = "";

            if (bugType == 0)
            {
                sql = "select row_number() over (order by a.id) as RN,a.id, a.Title,a.Contents,b.Uid,a.CreateDate from Bugs as a,Users as b where a.Createuser = b.Id and a.DeleteTag=0";

                DataTable dt = new DataTable();
                try
                {
                    dt = SQLHELP.GetDataTableNoParm(sql);
                    if (dt == null || dt.Rows.Count == 0)
                    {
                        return null;

                    }
                    else
                    {
                        return dt;
                    }
                }
                catch (Exception)
                {
                    Common.WriteFile(CommonCs.Exceptions.Abnormal, "获取所有buglist[不带分页]异常,msg:[" + Exceptions.getbuglistbybugtypeSql + "]!");
                    throw new Exception(Exceptions.getbuglistbybugtypeSql);
                }
            }
            else
            {
                sql = "select row_number() over (order by a.id) as RN,a.id, a.Title,a.Contents,b.Uid,a.CreateDate from Bugs as a,Users as b where a.Createuser = b.Id and a.DeleteTag=0 and a.BugType='" + bugType + "'";

                SqlParameter[] parm = new SqlParameter[]{
                    new SqlParameter("@BugType",bugType)
                };

                DataTable dt = new DataTable();
                try
                {
                    dt = SQLHELP.GetDataTableWithParm(sql, parm);
                    if (dt == null || dt.Rows.Count == 0)
                    {
                        return null;

                    }
                    else
                    {
                        return dt;
                    }
                }
                catch (Exception)
                {
                    Common.WriteFile(CommonCs.Exceptions.Abnormal, "根据type获取buglist[不带分页]异常,msg:[" + Exceptions.getbuglistbybugtypeSql + "]!");
                    throw new Exception(Exceptions.getbuglistbybugtypeSql);
                }
            }
        }

        public DataTable AjaxPageByIndex(int index, int size,int type)
        {
            string sql = "";
            if (type == 0)
            {
                sql = @"
                declare @pagesize int ;
                declare @pageindex int ;

                declare @start int;
                declare @end int;

                set @pagesize = " + size + @";
                set @pageindex = " + index + @";

                set @start = @pageindex * @pagesize + 1;
                set @end = @start + @pagesize - 1;

                select * from (
                select row_number() over (order by a.id) as RN,a.id, a.Title,a.Contents,b.Uid,a.CreateDate from Bugs as a,Users as b where a.Createuser = b.Id and a.DeleteTag=0) as ab where ab.RN between @start and @end;";
            }
            else
            {
                sql = @"
                declare @pagesize int ;
                declare @pageindex int ;

                declare @start int;
                declare @end int;

                set @pagesize = " + size + @";
                set @pageindex = " + index + @";

                set @start = @pageindex * @pagesize + 1;
                set @end = @start + @pagesize - 1;

                select * from (
                select row_number() over (order by a.id) as RN,a.id, a.Title,a.Contents,b.Uid,a.CreateDate from Bugs as a,Users as b where a.Createuser = b.Id and a.DeleteTag=0 and a.bugtype='"+type+"') as ab where ab.RN between @start and @end;";
            }

            DataTable dt = new DataTable();
            try
            {
                dt = SQLHELP.GetDataTableNoParm(sql);
                if (dt == null || dt.Rows.Count == 0)
                {
                    return null;

                }
                else
                {
                    return dt;
                }
            }
            catch (Exception)
            {
                Common.WriteFile(CommonCs.Exceptions.Abnormal, "分页控件翻页事件获取buglist异常,msg:[" + Exceptions.ajaxpagingerror + "]!");
                throw new Exception(Exceptions.ajaxpagingerror);
            }
        }

        public BugUser GetBugByBugId(int id)
        {
            String sql = "select a.CreateUser, a.Contents, a.Id,a.Title,a.ReplyCount,b.RealName,a.CreateDate from Bugs as a ,Users as b where a.DeleteTag ='0' and a.Createuser = b.Id and a.Id=@id";
            SqlParameter[] parm = new SqlParameter[]{
                new SqlParameter("@id",id)
            };
            DataTable dt = new DataTable();
            BugUser bu = new BugUser();
            try
            {
                dt = SQLHELP.GetDataTableWithParm(sql, parm);
                if (dt == null || dt.Rows.Count == 0)
                {
                    return null;
                }
                else
                {
                    bu.Id = Convert.ToInt32(dt.Rows[0]["Id"].ToString());
                    bu.Title = dt.Rows[0]["Title"].ToString();
                    bu.CreateUser = Convert.ToInt32(dt.Rows[0]["Createuser"].ToString());
                    bu.ReplyCount = Convert.ToInt32(dt.Rows[0]["ReplyCount"].ToString());
                    bu.Realname = dt.Rows[0]["RealName"].ToString();
                    bu.Createdate = Convert.ToDateTime(dt.Rows[0]["CreateDate"].ToString());
                    bu.Contents = dt.Rows[0]["Contents"].ToString();
                    bu.CreateUser = Convert.ToInt32(dt.Rows[0]["CreateUser"].ToString());
                    return bu;
                }
            }
            catch (Exception)
            {
                Common.WriteFile(CommonCs.Exceptions.Abnormal, "根据bugid获取buglist异常,msg:[" + Exceptions.getbugbybugid + "]!");
                throw new Exception(Exceptions.getbugbybugid);
            }
        }

        public int UpdateBugReplyCount(int id)
        {
            String sql1 = "select ReplyCount from Bugs where Id=@id";
            int replycount = 0;
            SqlParameter[] parm1 = new SqlParameter[]{
                new SqlParameter("@id",id)
            };
            DataTable dt = new DataTable();
            try
            {
                dt = SQLHELP.GetDataTableWithParm(sql1, parm1);
                if (dt != null || dt.Rows.Count != 0)
                {
                    replycount = Convert.ToInt32(dt.Rows[0]["ReplyCount"].ToString());
                }
            }
            catch (Exception)
            {
                Common.WriteFile(CommonCs.Exceptions.Abnormal, "根据id获取bug回复数量异常,msg:[" + Exceptions.getreplycountbybugid + "]!");
                throw new Exception(Exceptions.getreplycountbybugid);
            }

            replycount = replycount + 1;

            String sql2 = "update Bugs set ReplyCount = @ReplyCount where Id =@id";
            SqlParameter[] parm2 = new SqlParameter[]{
                new SqlParameter("@ReplyCount",id),
                new SqlParameter("@id",id)
            };

            int resultnum = 0;
            try
            {
                resultnum = SQLHELP.GetNonQueryWithParm(sql2, parm2);
            }
            catch (Exception)
            {
                resultnum = 0;
                Common.WriteFile(CommonCs.Exceptions.Abnormal, "根据id更新bug回复数量异常,msg:[" + Exceptions.updateBugReplyCount + "]!");
                throw new Exception(Exceptions.updateBugReplyCount);
            }
            return resultnum;
        }

        public DataTable GetBugListByTitle(string title)
        {
            string sql = "";
            if (title != "")
            {
                sql = "select a.id,a.Title,a.Contents from bugs as a where title like '%" + title + "%'";
            }
            else
            {
                sql = "select a.id,a.Title,a.Contents from bugs as a";
            }
            DataTable dt = new DataTable();
            try
            {
                dt = SQLHELP.GetDataTableNoParm(sql);
                if (dt != null || dt.Rows.Count != 0)
                {
                    return dt;
                }
                else {
                    return null;
                }
            }
            catch (Exception)
            {
                Common.WriteFile(CommonCs.Exceptions.Abnormal, "根查询title查询buglist异常,msg:[" + Exceptions.searchbuglistbytitle + "]!");
                throw new Exception(Exceptions.searchbuglistbytitle);
            }
        }

        public int DeleteBugById(int id)
        {
            //String sql = "delete Bugs ";
            return 0;
        }
    }
}
